package com.wframe.project.former.dataservices.marketing;

import com.frame.database.JBaseDao;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.frame.util.DateConvert;
import com.wframe.project.former.dataservices.products.dao.ProductClassDao;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/6/3.
 */
public class SingDao extends JBaseDao {

    public SingDao() {
        super("wb_promotion_flash_sale");
    }

    public PageResult querySinLe(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "select s.id,s.promotionid,s.count_down,s.price,s.amount,(select t.`name` from wb_products t where p.product_val=t.id) productname,p.platform,(select (ifnull(pr.amount,0)-ifnull(pr.djamount,0)) from wb_products_prices pr where pr.id=p.product_val) productamount,p.start_time,p.end_time,p.state,p.time,p.name promotionsname,p.remark,p.product_val,p.sort_value,p.description,UNIX_TIMESTAMP(p.start_time) startmsec,UNIX_TIMESTAMP(p.end_time) endmsec,UNIX_TIMESTAMP() newtime from wb_promotion_flash_sale s,wb_promotions p where s.promotionid=p.id and p.promotion_type=0 and (state=1 or state=0)";
        List parmlist = new ArrayList();
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("name"))) {
                sql += " and p.name like ?";
                parmlist.add("%" + parms.getString("name") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("platform"))) {
                sql += " and p.platform=?";
                parmlist.add(parms.getString("platform"));
            }
            if (StringUtils.isNotBlank(parms.getString("start"))) {
                sql += " and p.start_time>=?";
                parmlist.add(DateConvert.toDate(parms.getString("start")+" 00:00:00"));
            }
            if (StringUtils.isNotBlank(parms.getString("end"))) {
                sql += " and p.start_time<=?";
                parmlist.add(DateConvert.toDate(parms.getString("end")+" 23:59:59"));
            }

            //已暂停
            if (StringUtils.isNotBlank(parms.getString("state")) && parms.getString("state").equals("0")){
                sql +=" and state=0 and start_time<now() and end_time>NOW()";
            }
            //未开始
            if (StringUtils.isNotBlank(parms.getString("state")) && parms.getString("state").equals("1")){
                sql +=" and state=1 and start_time>now()";
            }
            //进行中
            if (StringUtils.isNotBlank(parms.getString("state")) && parms.getString("state").equals("2")){
                sql +=" and state =1 AND start_time<now() and end_time>NOW() ";
            }
            //已失效
            if (StringUtils.isNotBlank(parms.getString("state")) && parms.getString("state").equals("3")){
                sql +=" and (state =1 or state=0)  and  end_time<NOW()";
            }
        }
        sql += " order by p.sort_value desc,s.id desc";
        return getSession().query2(sql, parmlist, size, pageno);
    }

    public JdataBean selectSinLeByid(int singleid) throws Exception {
        return getSession().getObject("select s.id flashsaleid,s.promotionid,s.count_down,s.price,s.amount activityamount,s.name productname,p.sort_value,p.platform,p.product_val,p.district_description,p.show_sell,p.start_time,p.end_time,p.state,p.time,p.name promotionsname,p.remark,p.description from wb_promotion_flash_sale s,wb_promotions p where s.promotionid=p.id and s.id=?", new Object[]{singleid});
    }

    public JdataBean getClassByCode(String code) throws Exception {
        return getSession().getObject("select * from wb_products_class where code=?", new Object[]{code});
    }

    public PageResult queryChooseProduct(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "SELECT m.id,m.`name` pname,m.`code`,(select ifnull(pr.amount,0)-ifnull(pr.djamount,0) from  wb_products_prices pr where pr.productid=m.id) productamount from  wb_products m where m.`status`=2";
        List parmlist = new ArrayList();
        String[] codelist = null ;
        String code = "";
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("pname"))) {
                sql += " and m.name like ?";
                parmlist.add("%" + parms.getString("pname") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("code"))) {
                String codestr=parms.getString("code");
                sql += " and m.code in ("+ codestr +") ";
            }
            if (StringUtils.isNotBlank(parms.getString("classid"))) {
                if ("null".equals(parms.getString("classid"))) {
                    sql += " and m.id not in (select productid from wb_products_and_class)";
                } else {
                    JdataBean clsbean = new ProductClassDao().getBean(parms.getString("classid"));
                    sql += " and m.id in (select productid from wb_products_and_class where classcode like ? or classcode=?)";
                    parmlist.add(clsbean.getString("code") + ".%");
                    parmlist.add(clsbean.getString("code"));
                }
            }
        }
        sql += " group by m.id desc";
        return getSession().query2(sql, parmlist, size, pageno);
    }

    public List<JdataBean> queryType(int productId) throws Exception {
        String promotiontype = "";
        String sql = "SELECT promotion_type from wb_promotions WHERE platform in (0,1) and start_time < now() and end_time > now() and state=1 and "
                + " (product_type=0 or (product_type=1 and product_val=?)"
                + " or (product_type=2 and product_val=(SELECT promotionid from wb_promotion_product WHERE productid=?))"
                + " or (product_type=3 and product_val=(SELECT classid from wb_products_and_class WHERE productid=?)))";
        return getSession().query(sql, new Object[]{productId, productId, productId});
    }

    public void updatePromotions(int promotionsid) throws Exception {
        getSession().update("UPDATE wb_wb_promotions set price=?,amount=?,start_time=?,end_time=?,time=?,`name`=? where id=?", new Object[]{promotionsid});
    }

    public String addControlsalesSingLe(JdataBean bean) throws Exception {
        return getSession().saveReturnKey("wb_promotion_controlsale", bean.getDataMap());
    }

    public void deleteControlsalesAll(String promotionid) throws HibernateException, Exception{
        getSession().delete("delete from wb_promotion_controlsale where promotion_id=?",new Object[]{promotionid});
    }



    public void updatPromotecontrol(String protomctid,int iscontrol) throws Exception{
        getSession().update("update wb_promotions set is_control=? where id=?",new Object[]{iscontrol,protomctid});
    }

    public PageResult checkProduct(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "SELECT id,`name` pname,`code`,types,(select (ifnull(pr.amount,0)-ifnull(pr.djamount,0)) from  wb_products_prices pr where pr.id=n.product_val) productamount from(SELECT *from wb_products "
                + "WHERE id in (SELECT DISTINCT(product_val) from wb_promotions WHERE product_type=1 and promotion_type=0))m "
                + "join (SELECT GROUP_CONCAT(case promotion_type when '0' THEN '单品促销' when '1' THEN "
                + "'买送促销' when '2' THEN '满减促销' when '3' THEN '满赠促销' end) types,product_val "
                + "from (SELECT * from wb_promotions WHERE product_type=1 and product_val "
                + "in (SELECT DISTINCT(product_val) from wb_promotions WHERE product_type=1 and promotion_type=0))t "
                + "GROUP BY product_val)n on m.id=n.product_val";
        sql += " ORDER BY m.id DESC";
        return getSession().query(sql, null, size, pageno);
    }

    public JdataBean queryproduct(int proid) throws Exception{
        return getSession().getObject("select id,name from wb_products where id=?",new Object[]{proid});
    }

    public JdataBean querySelectedProduct(int id) throws Exception{
        String sql = "SELECT m.id,m.`name` name,m.`code`,(select ifnull(pr.amount,0)-ifnull(pr.djamount,0) from  wb_products_prices pr where pr.productid=m.id) productamount from  wb_products m where m.`status`=2 and m.id=?";
        return getSession().getObject(sql,new Object[]{id});
    }
}
